[Your Name]
[Instructor Name]
[Course Number]




















                                                                  
Write a query on the employees table that identify employees who have no middle name.
SELECT CONCAT ( First_Name, CONCAT(' ', Last_Name)) employee_name
 FROM EMPLOYEE
   ORDER by employee_name;

EMPLOYEE_NAME
————————————————————————

Identify SSN of employees who historically had salaries between $60000 and

 $175000.
SELECT 
FROM EMPLOYEE
WHERE HISTORY_BENEFITS BETWEEN (SELECT 60000) AND 175000	


Provide a list of employee names and departments for people in accounting,
marketing or production.   Order the output by department, last name and first name.   Don't use "or".   Use “in function”.
SELECT First_Name, Last_Name, Dpt_Id
FROM EMPLOYEE 
WHERE Dpt_Id = (SELECT Dpt_Id
FROM DEPARTMENT
WHERE DEPARTMENT = ‘marketing’, ‘production’, ‘accounting);



Output
First_Name	Last_Name	Dpt_Id

Melvin          Sharply        000
Mary            Jones		 011
Iim             Wilson	       012
Mike   	    Taylor         013
Celia           Mayer          014
		        	
				

Provide a query to determine if there are any employees who don't have any phone numbers.   Use minus.   Use SSN.
(SELECT First_Name, Last_Name, position FROM EMPLOYEE
MINUS
SELECT Phone_Number, Emp_SSN, Positions FROM PHONE_NUMBER)
(SELECT Phone_Number, Emp_SSN, Positions FROM PHONE_NUMBER
MINUS
SELECT First_Name, Last_Name, position FROM EMPLOYEE) 

Provide a query to identify any employees whose name starts with BROWN.    Concatenate the output lastname, firstname and mi so it would look like this, including spaces, the period and case.   Use initcap.
SELECT First_Name, Last_Name INITCAP(First_Name) "First Name",
INITCAP(Last_Name) "Last Name"
FROM   EMPLOYEE
WHERE First_Name LIKE ' BROWN%'
ORDER by First_Name





Output 

FIRST_NAME     LAST_NAME      First Name     Last Name
——————————     ———————————    ———————————    —————————————
Miguel         Brown          Brown          Miguel




Provide a query to determine if there are any employees whose current salary is greater 

than or equal to all historical salaries. Use a subquery with max() function.

SELECT 
FROM EMPLOYEE
WHERE Emp_SSN IN 
(SELECT Emp_SSN
FROM EMPLOYEE  
WHERE HISTORY_BENEFITS = 
(SELECT MAX(HISTORY_BENEFITS) 
FROM EMPLOYEE
WHERE HISTORY_BENEFITS < 
(SELECT MAX(HISTORY_BENEFITS) 
FROM EMPLOYEE)));


Output

 Emp_SSN           MAX(SALARY)
———————————        ———————————
 123456789          225000
 123456793          130000
 123456789          200000
 123456793          100000
 123456796          40000

Provide a query to determine if there are any employees whose current salary is greater than or equal to all historical salaries and whose bonus is greater than or equal to all historical bonuses.  
SELECT COUNT(Emp_SSN), Dpt_Id, Salary_Level
FROM EMPLOYEE
GROUP BY Dpt_Id, Salary_Level
HAVING (COUNT(Emp_SSN) > 000
            OR Salary_Level >= Historical Salaries
           AND Bonus >= Historical Bonuses)
ORDER BY Dpt_Id, HISTORY_BENEFITS


Using CTAS create a copy of employee called employee_work that includes only personnel born on or after 3/7/64.  Now use a merge to update the people in that table’s age by 60 days or to insert them if they’re not there.

CREATE TABLE employee_work
WITH
(
    CLUSTERED COLUMNSTORE INDEX,
    DISTRIBUTION = HASH(Emp_SSN),
    PARTITION
    (
        OrderDateKey RANGE RIGHT FOR VALUES
        (
          2/7/74,3/7/84,4/7/90,5/7/94,7/9/84,8/7/74,2/7/94)))
AS SELECT * FROM EMPLOYEE;

Rename the Table for swapping in the new table and drop old one
RENAME OBJECT employee_work TO EMPLOYEE;
RENAME OBJECT EMPLOYEE TO employee_work;

DROP TABLE EMPLOYEE;
Provide a query to sum current salary and bonus grouped by department.
SELECT
    Emp_SSN, Salary_Level, Bonus,
    (Salary_Level + ((Salary_Level * Bonus) / 100)) as "Total_Salary" 
FROM CURRENT_BENEFITS;

Provide a query to sum current salary and bonus grouped by department where sum of salary is greater than 500000 and sum of bonus greater than 300000.
SELECT SUM(salary)) 
 FROM CURRENT_BENEFITS
 WHERE SUM(salary) >= 500000 SUM(Bonus) >= 300000
 GROUP by Dpt_Id                   

Provide a query to count employees grouped by department.   Exclude department with less than 2 employees from the output.
SELECT COUNT(Emp_SSN), Dpt_Id

  FROM EMPLOYEE
  GROUP BY Dpt_Id
  ORDER BY Dpt_Id;
 

OUTPUT

COUNT(Emp_SSN)      Dpt_Id
——————————————————  —————————————
 123456789          000
 123456793          012
 123456789          000
 123456793          012
 123456796          012            
                              
                             
                  
Provide a query to concatenate an employee's full name. Order output by last name, first 

name, mi.

SELECT CASE 
    WHEN mid_name IS NULL OR TRIM(Midddle_Name) ='' THEN
        CONCAT_WS( " ", First_Name, Last_Name ) 
    ELSE 
        CONCAT_WS( " ", First_Name, Middle_Name, Last_Name ) 
    END 
FROM EMPLOYEE;


Provide a query to replace type phone with work where it says office and residence where it says home. Use the decode function.
SELECT Phone_Number, Type_Phone
FROM
  PHONE_NUMBERS
ORDER BY
  DECODE ('office', work, home, 'residence');






Provide a query to concatenate an employee's full name in upper case.

SELECT DISTINCT UPPER (First_Name, Last_Name) "Uppercase Employee Name"
  FROM EMPLOYEE
  ORDER by UPPER (First_Name, Last_Name);                  




Provide a query to return employee's current salary or 0 for nulls whose SSN has 5 as the 
eighth digit. Use nvl() function. 
SELECT AVG (NVL(salary, 0)) avg_salary
  FROM EMPLOYEE;
 COUNT(Emp_SSN)

AVG_SALARY
———————————————
880181.8182


Provide an update statement to increase each employees current salary and bonus by 15%   if their salary is above average for employees. Commit the change.
SELECT First_Name, Last_Name, 1.15*SALARY, 1.15*BONUS;
FROM EMPLOYEE
WHERE SSN=ESSN AND PNO=PNUMBER AND PNAME= ‘CURRENT_SALARY’




Provide a command to create a complete table copy of the employee table.
CREATE TABLE EMPLOYEE_copy
AS 
SELECT Emp_SSN, First_Name, Last_Name, Dpt_Id, Mngr_SSN FROM EMPLOYEE WHERE 1=0;
Provide a query to return each male employees concatenated full name if their salary is above the average current salary. Use a subquery.

SELECT First_Name, Last_Name, Dpt_Id, CURRENT_SALARY, avg(CURRENT_SALARY)
FROM EMPLOYEE
GROUP BY First_Name, Last_Name, Dpt_Id, CURRENT_SALARY
HAVING CURRENT_SALARY > (select avg(CURRENT_SALARY))